<?php


namespace app\admin\controller;


use app\admin\model\Alternative;
use app\BaseController;
use app\Request;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use think\Exception;
use think\facade\Config;
use think\facade\Db;
use think\facade\View;

class Dictionaries extends BaseController
{
    protected $data = null;

    public function initialize()
    {
        parent::initialize();
        $this->data = Alternative::field('\'alternatives\' as table_name,\'请假字典\' as dic_name')->union([
            'SELECT \'apply_types\' as table_name,\'申请字典\' as apply FROM apply_types',
            'SELECT \'archive_type\' as table_name,\'员工档案\' as archive FROM archive_type',
            'SELECT \'customer_industry\' as table_name,\'客户行业字典\' as industry FROM customer_industry',
            'SELECT \'customer_intention\' as table_name,\'客户意向产品\' as intention FROM customer_intention',
            'SELECT \'customer_level\' as table_name,\'客户等级\' as level FROM customer_level',
            'SELECT \'customer_ripe\' as table_name,\'客户成熟度\' as ripe FROM customer_ripe',
            'SELECT \'customer_source\' as table_name,\'客户来源\' as source FROM customer_source',
            'SELECT \'customer_status\' as table_name,\'客户状态\' as status FROM customer_status',
            'SELECT \'customer_type\' as table_name,\'客户类别\' as type FROM customer_type',
            'SELECT \'follow_stage\' as table_name,\'跟进阶段\' as stage FROM follow_stage',
            'SELECT \'follow_status\' as table_name,\'跟进方式\' as status FROM follow_status',
            'SELECT \'journal_levels\' as table_name,\'周报等级\' as levels FROM journal_levels',
            'SELECT \'journal_types\' as table_name,\'周报类别\' as types FROM journal_types',
            'SELECT \'project_plans\' as table_name,\'项目计划\' as plans FROM project_plans',
            'SELECT \'cg_type\' as table_name,\'采购类型\' as cg FROM cg_type'
        ])->select()->toArray();
        View::assign('data', $this->data);
    }

    //资源显示
    public function index(Request $request)
    {
        if ($request->isAjax()) {
            //获取每页显示的条数
            $limit = $request->param('limit');
            //获取当前页数
            $page = $request->param('page');
            $result = Alternative::field('id,title,\'alternatives\' as table_name,\'请假字典\' as dic_name')->unionAll([
                'SELECT id,name,\'apply_types\' as table_name,\'申请字典\' as apply FROM apply_types',
                'SELECT id,name,\'archive_type\' as table_name,\'员工档案\' as archive FROM archive_type',
                'SELECT id,name,\'customer_industry\' as table_name,\'客户行业字典\' as industry FROM customer_industry',
                'SELECT id,name,\'customer_intention\' as table_name,\'客户意向产品\' as intention FROM customer_intention',
                'SELECT id,name,\'customer_level\' as table_name,\'客户等级\' as level FROM customer_level',
                'SELECT id,name,\'customer_ripe\' as table_name,\'客户成熟度\' as ripe FROM customer_ripe',
                'SELECT id,name,\'customer_source\' as table_name,\'客户来源\' as source FROM customer_source',
                'SELECT id,name,\'customer_status\' as table_name,\'客户状态\' as status FROM customer_status',
                'SELECT id,name,\'customer_type\' as table_name,\'客户类别\' as type FROM customer_type',
                'SELECT id,name,\'follow_stage\' as table_name,\'跟进阶段\' as stage FROM follow_stage',
                'SELECT id,name,\'follow_status\' as table_name,\'跟进方式\' as status FROM follow_status',
                'SELECT id,name,\'journal_levels\' as table_name,\'周报等级\' as levels FROM journal_levels',
                'SELECT id,name,\'journal_types\' as table_name,\'周报类别\' as types FROM journal_types',
                'SELECT id,name,\'project_plans\' as table_name,\'项目计划\' as plans FROM project_plans',
                'SELECT id,name, \'cg_type\' as table_name,\'采购类型\' as cg FROM cg_type'
            ])->select()->toArray();
            $count = count($result);//总条数
            $start = ($page - 1) * $limit;//偏移量，当前页-1乘以每页显示条数
            $result = array_slice($result, $start, $limit);
            return json(['code' => 200, 'msg' => '', 'total' => $count, 'data' => $result]);
        }
        return View::fetch();
    }


    //新增
    public function add(Request $request)
    {
        if ($request->isAjax()) {
            $data = [];
            $columnList = Db::name($request->param('table_name'))->select();
            if ($columnList->column('name')) { //检测字段是否存在
                $data['name'] = $request->param('name');
            } else {
                $data['title'] = $request->param('name');
            }
            $res = Db::name($request->param('table_name'))->save($data);
            if ($res) {
                return json(['code' => 200, 'url' => '', 'msg' => '添加成功！']);
            }
        }
        return View::fetch();
    }


    //删除
    public function del(Request $request)
    {
        $object = $request->param('object');
        if ($object) {
            foreach ($object as $key => $value) {
                Db::name($value['table'])->delete($value['id']);
            }
        }
        return json(['code' => 200, 'url' => '', 'msg' => '删除成功！']);
    }

    //编辑
    public function edit(Request $request)
    {
        $id = $request->param('id');
        $table = $request->param('table') ?? $request->param('table_name');
        $model = Db::name($table)->find($id);
        $model['name'] = $model['name'] ?? $model['title'];
        if ($request->isAjax()) {
            $data = [];
            $columnList = Db::name($table)->select();
            if ($columnList->column('name')) { //检测字段是否存在
                $data['name'] = $request->param('name');
            } else {
                $data['title'] = $request->param('name');
            }
            $data['id'] = $request->param('id');
            $res = Db::name($table)->update($data);
            if ($res) {
                return json(['code' => 200, 'url' => '', 'msg' => '编辑成功！']);
            }
        }
        View::assign('id', $id);
        View::assign('table', $table);
        View::assign('model', $model);
        return View::fetch();
    }

    //导入
    public function export(Request $request)
    {
        if ($request->isAjax()) {
            $file = $request->file('file');
            $filePath = \think\facade\Filesystem::disk('public')->putFile('excel', $file, 'uniqid');
            //实例化reader
            $ext = pathinfo($filePath, PATHINFO_EXTENSION);
            if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
                $this->error(('Unknown data format'));
            }
            if ($ext === 'csv') {
                $file = fopen($filePath, 'r');
                $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
                $fp = fopen($filePath, "w");
                $n = 0;
                while ($line = fgets($file)) {
                    $line = rtrim($line, "\n\r\0");
                    $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                    if ($encoding != 'utf-8') {
                        $line = mb_convert_encoding($line, 'utf-8', $encoding);
                    }
                    if ($n == 0 || preg_match('/^".*"$/', $line)) {
                        fwrite($fp, $line . "\n");
                    } else {
                        fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                    }
                    $n++;
                }
                fclose($file) || fclose($fp);

                $reader = new Csv();
            } elseif ($ext === 'xls') {
                $reader = new Xls();
            } else {
                $reader = new Xlsx();
            }
            $table = $request->post('table');
            $columnList = Db::name($table)->select();
            if ($columnList->column('name')) { //检测字段是否存在
                $name = 'name';
            } else {
                $name = 'title';
            }
            $fieldArr = ['名称' => $name];
            //加载文件
            $insert = [];
            try {
                if (!$PHPExcel = $reader->load('storage/' . $filePath)) {
                    return json(['code' => 400, 'url' => '', 'msg' => 'Unknown data format']);
                }
                $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
                $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
                $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
                $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
                $fields = [];
                for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                    for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                        $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                        $fields[] = $val;
                    }
                }

                for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                    $values = [];
                    for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                        $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                        $values[] = is_null($val) ? '' : $val;
                    }

                    $row = [];
                    $temp = array_combine($fields, $values);
                    foreach ($temp as $k => $v) {
                        if (isset($fieldArr[$k]) && $k !== '') {
                            $row[$fieldArr[$k]] = $v;
                        }
                    }
                    if ($row) {
                        $insert[] = $row;
                    }
                }
            } catch (Exception $exception) {
                return json(['code' => 400, 'url' => '', 'msg' => $exception->getMessage()]);
            }
            if (!$insert) {
                return json(['code' => 400, 'url' => '', 'msg' => 'No rows were updated！']);
            }
            try {
                Db::name($table)->insertAll($insert);
                return json(['code' => 200, 'url' => '', 'msg' => '成功！']);
            } catch (\Exception $e) {
                $msg = $e->getMessage();
                if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
                    $msg = "导入失败，包含【{$matches[1]}】的记录已存在";
                };
                return json(['code' => 400, 'url' => '', 'msg' => $msg]);
            }
        }

        return View::fetch();
    }
}